package Network

import (
	"SQL/FSDB"
	"database/sql"
	"logs"
	"strings"
	"time"
)

func ficowInit(dbQueue chan *sql.DB) {
	FSDB.Init(dbQueue)
	var err error
	updateUserSessionFuncSQL := `CREATE OR REPLACE FUNCTION updateUserSession(sender bigint,receiver bigint,msgID bigint) RETURNS void AS $$
							DECLARE
								sessionID bigint;
							BEGIN
								SELECT INTO sessionID id 
								FROM sessions 
								WHERE (senderID=sender AND receiverID=receiver)
									OR (senderID=receiver AND receiverID=sender);

								IF sessionID IS NOT NULL THEN
									UPDATE sessions
									SET senderID=sender,receiverID=receiver,lastMsgID=msgID
									WHERE id=sessionID;
								ELSE
									INSERT INTO sessions(senderID,receiverID,lastMsgID)
									VALUES(sender,receiver,msgID);
								END IF;
							END;
							$$ LANGUAGE plpgsql;`

	updateGroupSessionFuncSQL := `CREATE OR REPLACE FUNCTION updateGroupSession(sender bigint,groupp bigint,msgID bigint) RETURNS void AS $$
							DECLARE
								sessionID bigint;
							BEGIN
								SELECT INTO sessionID id 
								FROM sessions 
								WHERE receiverID=groupp;

								IF sessionID IS NOT NULL THEN
									UPDATE sessions
									SET senderID=sender,receiverID=groupp,lastMsgID=msgID
									WHERE id=sessionID;
								ELSE
									INSERT INTO sessions(senderID,receiverID,lastMsgID)
									VALUES(sender,groupp,msgID);
								END IF;
							END;
							$$ LANGUAGE plpgsql;`

	getFriendSessionFuncSQL := `
			CREATE OR REPLACE FUNCTION getFriendSession(currentUser bigint,
				out talkerID bigint,out talkerName text,out talkerIconURL text,out senderID bigint,
				out msgID bigint,out msgType text,out msg text,out createTime TIMESTAMP WITH TIME ZONE,out unreadNum bigint)

			RETURNS SETOF RECORD AS $$
				DECLARE
					sessionInfo RECORD;
					lastOnlineTime  TIMESTAMP WITH TIME ZONE;
				BEGIN
					FOR sessionInfo IN
						SELECT s.*
						FROM sessions s,objects o
						WHERE
						(s.senderID=currentUser OR s.receiverID=currentUser)
						AND o.id = s.receiverID AND o.isUser=true
					LOOP
						-- RAISE NOTICE 'sessionID: %',sessionInfo.id;
						
						-- last msg info  ---------------------------------------------

						SELECT INTO msgID,senderID,msgType,msg,createTime id,sender,type,content,m.createTime 
						FROM messages m 
						WHERE sessionInfo.lastMsgID = m.id;
						
						-- RAISE NOTICE 'id:%,sender:%,type:%,content:%,createTime:%',msgID,senderID,msgType,msg,createTime;
						
						-- talker info  ---------------------------------------------
						IF sessionInfo.senderID = currentUser THEN
							talkerID := sessionInfo.receiverID;
						ELSE
							talkerID := sessionInfo.senderID;
						END IF;
						
						SELECT INTO talkerName,talkerIconURL o.nickname,o.iconURL
						FROM objects o,users u
						WHERE o.id = talkerID AND u.id = o.id;
                        
                        -- RAISE NOTICE 'talker:%,nickname:%,iconURL:%',talkerID,talkerName,talkerIconURL;
                                                
                        -- user lastOnlineTime info  ---------------------------------------------
						
						SELECT INTO lastOnlineTime u.lastOnlineTime
						FROM users u
						WHERE u.id = currentUser;
						
						-- RAISE NOTICE 'lastOnlineTime:%',lastOnlineTime;
                        
						-- unread msg num ---------------------------------------------
						
						SELECT INTO unreadNum COUNT(*) 
						FROM
						((SELECT id FROM messages m
						WHERE sender=currentUser AND receiver=talkerID AND m.createTime > lastOnlineTime)
						UNION ALL
						(SELECT id FROM messages m
						WHERE sender=talkerID AND receiver=currentUser AND m.createTime > lastOnlineTime)) temp;
						
						-- RAISE NOTICE 'unreadNum:%',unreadNum;
						
						-- ---------------------------------------------
						RETURN NEXT;

					END LOOP;
				END;
			$$ LANGUAGE plpgsql;
	`
	getGroupSessionFuncSQL := `
			CREATE OR REPLACE FUNCTION getGroupSession(currentUser bigint,
				out groupID bigint,out groupName text,out groupIconURL text,out senderID bigint,out senderName text,
				out msgID bigint,out msgType text,out msg text,out createTime TIMESTAMP WITH TIME ZONE,out unreadNum bigint)

			RETURNS SETOF RECORD AS $$
				DECLARE
					sessionInfo RECORD;
					lastOnlineTime  TIMESTAMP WITH TIME ZONE;
				BEGIN
					FOR sessionInfo IN
						SELECT s.*
						FROM members m,sessions s
						WHERE m.userID=currentUser AND s.receiverID=m.groupID
					LOOP
						-- RAISE NOTICE 'sessionID: %',sessionInfo.id;
						
						-- last msg info  ---------------------------------------------

						SELECT INTO msgID,senderID,senderName,msgType,msg,createTime m.id,sender,o.nickname,type,content,m.createTime 
						FROM messages m,objects o
						WHERE sessionInfo.lastMsgID = m.id AND o.id=sessionInfo.senderID;
						
						-- RAISE NOTICE 'id:%,sender:%,type:%,content:%,createTime:%',msgID,senderID,msgType,msg,createTime;
						
						-- group info  ---------------------------------------------
						groupID := sessionInfo.receiverID;
						
						SELECT INTO groupName,groupIconURL o.nickname,o.iconURL
						FROM objects o
						WHERE o.id = groupID;
						
						-- RAISE NOTICE 'talker:%,nickname:%,iconURL:%',groupID,groupName,groupIconURL;
						
						-- user lastOnlineTime info  ---------------------------------------------
						
						SELECT INTO lastOnlineTime u.lastOnlineTime
						FROM users u
						WHERE u.id = currentUser;
						
						RAISE NOTICE 'lastOnlineTime:%',lastOnlineTime;
						
						-- unread msg num ---------------------------------------------
						
						SELECT INTO unreadNum COUNT(*) 
						FROM messages m
						WHERE receiver=groupID AND m.createTime > lastOnlineTime;
						
						-- RAISE NOTICE 'unreadNum:%',unreadNum;
						
						-- ---------------------------------------------
						RETURN NEXT;

					END LOOP;
				END;
			$$ LANGUAGE plpgsql;
				`
	_, err = FSDB.Exec(getFriendSessionFuncSQL)
	_, err = FSDB.Exec(getGroupSessionFuncSQL)
	_, err = FSDB.Exec(updateUserSessionFuncSQL)
	_, err = FSDB.Exec(updateGroupSessionFuncSQL)
	if err != nil {
		logs.Print("FSDB 初始化错误：", err.Error())
		panic(err.Error())
	}
}

func currentTime() string {
	loc, _ := time.LoadLocation("Asia/Hong_Kong") //参数就是解压文件的“目录”+“/”+“文件名”。
	nowStr := time.Now().In(loc).String()
	nowStrs := strings.Split(nowStr, ".")
	return nowStrs[0]
}
